use stuDB

if object_id('Card') is not null
	begin
		drop table Card
	end
CREATE  TABLE  Card
(
	CNo int identity(1,1) not null,
	Name varchar(25) not null,
	ClassName varchar(50) not null
) 
GO

if object_id('Books') is not null
	begin
		drop table Books
	end
CREATE  TABLE  Books
(
	BNo int identity(100,1) not null,
	BName varchar(200) not null,
	Author varchar(50) not null,
	Price money not null,
	Quantity int not null
) 
GO

if object_id('Borrow') is not null
	begin
		drop table Borrow
	end
CREATE  TABLE  Borrow
(
	CNo int not null,
	BNo int not null,
	RDate datetime not null,
	BDate datetime not null
) 
GO

alter table Books
	add 
		constraint PK_Books_BNo primary key(BNo)

alter table Card
	add 
		constraint PK_Card_CNo primary key(CNo)

alter table Borrow
	add 
		constraint PK_Borrow_CNoBNo primary key(CNo,BNo)

alter table Borrow
	add 
		constraint FK_Borrow_Books_BNo foreign key(BNo) references Books(BNo),
		constraint FK_Borrow_Card_CNo foreign key(CNo) references Card(CNo)


INSERT INTO Card(Name,ClassName) VALUES('张三','C01'),
											 ('李四','C01'),
											 ('王丽','C01'),
											 ('王毛子','C02'),
											 ('赵六','C02'),
											 ('鸟人','C02'),
											 ('钱前','C03'),
											 ('小王八','C03'),
											 ('傻屌','C03'),
											 ('大王八','C04');

INSERT INTO Books(BName,Author,Price,Quantity) VALUES('Java数据结构和算法(第二版·影印版)','三儿',50.22,5),
													 ('SQL Server 从入门到精通（第2版）','三儿',50.22,5),
													 ('Spring Boot实战','三儿',50.22,5),
													 ('SSM轻量级框架应用实战','三儿',50.22,5),
													 ('语文','文老师',10.50,30),
													 ('数学','学老师',20.60,30),
													 ('英语','语老师',18.20,30),
													 ('水浒传','施耐庵',66.66,66),
													 ('三国志','罗贯中',77.77,20),
													 ('红楼梦','曹雪芹',88.88,8),
													 ('西游记','吴承恩',99.99,10),
													 ('网络教育','好老师',20.22,9),
													 ('傻逼如何炼成的','程一峰',222,1);

INSERT INTO Borrow(CNo,BNo,RDate,BDate) VALUES (1,100,'2018-02-20 00:00:00','2018-05-03 09:17:55'),
											   (1,101,'2018-03-28 00:00:00','2018-05-10 07:35:22'),
											   (2,102,'2018-04-11 00:00:00','2018-05-01 01:15:42'),
											   (2,103,'2018-04-20 00:00:00','2018-05-03 05:25:32'),
											   (3,100,'2018-04-22 00:00:00','2018-05-02 04:05:58'),
											   (3,102,'2018-01-24 00:00:00','2018-05-20 07:26:32'),
											   (4,104,'2018-04-10 00:00:00','2018-05-03 08:12:44'),
											   (4,105,'2018-04-03 00:00:00','2018-05-02 06:13:13'),
											   (5,106,'2018-05-22 00:00:00','2018-05-05 04:14:02'),
											   (6,105,'2018-01-25 00:00:00','2018-03-11 11:12:03'),
											   (7,110,'2017-04-28 00:00:00','2018-03-16 11:12:11'),
											   (8,100,'2017-03-20 00:00:00','2018-03-16 09:12:05'),
											   (9,107,'2017-02-27 00:00:00','2018-01-20 07:02:14'),
											   (9,109,'2017-01-10 00:00:00','2018-01-15 07:02:14');

update Books SET quantity=quantity-(select count(*) from Borrow where BNo=Books.BNo);  --更新库存

SELECT CNo AS '借书卡号',COUNT(BNo) AS '借图书册数' FROM Borrow GROUP BY CNo HAVING COUNT(BNo)>=5; --找出借书超过5本的读者,输出借书卡号及所借图书册数。

SELECT Name AS '姓名',ClassName AS '班级名称' FROM [Card] WHERE CNo IN (SELECT CNo FROM Borrow WHERE BNo=(SELECT BNo FROM Books  WHERE BName='水浒传')); --查询借阅了"水浒传"一书的读者，输出读者姓名及所在班级。

SELECT * FROM BORROW WHERE RDATE<GETDATE(); -- 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

SELECT BNO AS '书号',BNAME AS '书名',AUTHOR AS '作者' FROM BOOKS WHERE BNAME LIKE '%网络%'; --查询书名包括"网络"关键词的图书，输出书号、书名、作者。

SELECT BNO AS '书号',BNAME AS '书名',AUTHOR AS '作者' FROM BOOKS WHERE PRICE=(SELECT MAX(PRICE) FROM BOOKS);--查询现有图书中价格最高的图书，输出书名及作者。

SELECT a.CNO FROM Borrow a,Books b WHERE a.BNo=b.BNo AND b.BName='Java数据结构和算法(第二版·影印版)'AND 
NOT EXISTS(SELECT * FROM Borrow aa,Books bb WHERE aa.BNo=bb.BNo AND bb.BName='SQL Server 从入门到精通（第2版）' AND aa.CNo=a.CNo)
ORDER BY a.CNo DESC;  -- 查询当前借了"Java数据结构和算法(第二版·影印版)"但没有借"SQL Server 从入门到精通（第2版）"的读者，输出其借书卡号，并按卡号降序排序输出。

UPDATE Borrow SET RDate=(SELECT  DATEADD(DAY,7,GETDATE())) WHERE CNo IN (SELECT CNo FROM [Card] WHERE ClassName='C01');--将"C01"班同学所借图书的还期都延长一周。

DELETE FROM Books WHERE BNo IN (SELECT BNo FROM Books WHERE BNo NOT IN (SELECT BNo FROM Borrow));--从Books图书表中删除当前无人借阅的图书记录。

SELECT CNo AS '借记卡号' FROM Borrow 
WHERE CNo IN (SELECT CNo FROM Borrow 
WHERE BNo=(SELECT BNo FROM Books WHERE BName='SpringBoot实战')) 
AND BNo=(SELECT BNo FROM Books WHERE BName='SSM轻量级框架应用实战') ORDER BY CNo;--查询当前同时借有"Spring Boot实战"和" SSM轻量级框架应用实战"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
